/*
 *  Copyright 2025 ruoyi.ai. All rights reserved.
 */
package org.ruoyi.interview.util;

import java.io.*;
import java.nio.file.*;
import java.util.regex.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 面试题JSON转换为SQL工具类
 * 将interview_questions目录下的JSON文件转换为SQL插入语句
 *
 * @author lingma
 */
public class InterviewQuestionConverter {

    // JSON文件目录
    private static final String JSON_DIR = "interview_questions";
    // SQL输出目录
    private static final String SQL_OUTPUT_DIR = "generated_sql";

    /**
     * 主方法，执行转换过程
     */
    public static void main(String[] args) {
        try {
            System.out.println("开始转换面试题JSON文件为SQL...");
            convertJsonToSQL();
            System.out.println("转换完成！生成的SQL文件位于 " + SQL_OUTPUT_DIR + " 目录中");
        } catch (Exception e) {
            System.err.println("转换过程中发生错误: " + e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * 将JSON文件转换为SQL语句
     */
    public static void convertJsonToSQL() throws Exception {
        // 创建输出目录
        Path outputPath = Paths.get(SQL_OUTPUT_DIR);
        if (!Files.exists(outputPath)) {
            Files.createDirectory(outputPath);
            System.out.println("创建输出目录: " + SQL_OUTPUT_DIR);
        }

        // 获取所有JSON文件
        File jsonDir = new File(JSON_DIR);
        File[] jsonFiles = jsonDir.listFiles((dir, name) -> name.endsWith(".json"));

        if (jsonFiles == null || jsonFiles.length == 0) {
            System.out.println("未找到JSON文件在目录: " + JSON_DIR);
            return;
        }

        System.out.println("找到 " + jsonFiles.length + " 个JSON文件待处理");

        // 创建总的SQL文件
        File allSqlFile = new File(SQL_OUTPUT_DIR + "/all_interview_questions.sql");
        try (BufferedWriter allWriter = new BufferedWriter(new FileWriter(allSqlFile))) {
            // 写入SQL文件头部信息
            allWriter.write("-- 面试问题SQL导入文件\n");
            allWriter.write("-- 自动生成于: " + new java.util.Date() + "\n\n");

            // 处理每个JSON文件
            for (File jsonFile : jsonFiles) {
                processJsonFile(jsonFile, allWriter);
            }
        }

        System.out.println("已生成总SQL文件: " + allSqlFile.getName());
    }

    /**
     * 处理单个JSON文件
     */
    private static void processJsonFile(File jsonFile, BufferedWriter allWriter) throws Exception {
        System.out.println("处理文件: " + jsonFile.getName());

        // 从文件名提取分类ID (cid)
        String fileName = jsonFile.getName();
        int categoryId = Integer.parseInt(fileName.replaceAll("question_list_cid_(\\d+)\\.json", "$1"));

        // 读取JSON文件内容
        String content = new String(Files.readAllBytes(jsonFile.toPath()));

        // 提取问题列表部分
        Pattern questionListPattern = Pattern.compile("\"questionList\"\\s*:\\s*\\[(.*?)\\]", Pattern.DOTALL);
        Matcher questionListMatcher = questionListPattern.matcher(content);

        if (questionListMatcher.find()) {
            String questionListStr = questionListMatcher.group(1);

            // 为每个分类创建单独的SQL文件
            String sqlFileName = SQL_OUTPUT_DIR + "/" + fileName.replace(".json", ".sql");
            try (BufferedWriter categoryWriter = new BufferedWriter(new FileWriter(sqlFileName))) {
                // 写入文件头部信息
                categoryWriter.write("-- " + fileName + " 的面试问题SQL导入文件\n");
                categoryWriter.write("-- 分类ID: " + categoryId + "\n\n");

                // 写入分类ID设置语句
                categoryWriter.write("-- 设置分类ID\n");
                categoryWriter.write("SET @category_id = " + (1000 + categoryId - 1) + ";\n\n");

                // 写入插入语句头部
                categoryWriter.write("INSERT INTO interview_question (tenant_id, content, type, difficulty, category_id, answer, analysis, status, del_flag, create_dept, create_by, create_time, update_by, update_time, remark) VALUES\n");
                allWriter.write("-- " + fileName + " 的面试问题\n");
                allWriter.write("SET @category_id = " + (1000 + categoryId - 1) + ";\n");
                allWriter.write("INSERT INTO interview_question (tenant_id, content, type, difficulty, category_id, answer, analysis, status, del_flag, create_dept, create_by, create_time, update_by, update_time, remark) VALUES\n");

                // 提取所有问题
                List<String> valuesList = extractQuestions(questionListStr);

                if (valuesList.isEmpty()) {
                    // 写入一个空值作为示例
                    categoryWriter.write("('000000', '示例问题', '简答题', '中等', @category_id, '', '示例分析', '0', '0', 103, 1, NOW(), 1, NOW(), NULL);\n\n");
                    allWriter.write("('000000', '示例问题', '简答题', '中等', @category_id, '', '示例分析', '0', '0', 103, 1, NOW(), 1, NOW(), NULL);\n\n");
                } else {
                    // 写入所有值
                    for (int i = 0; i < valuesList.size(); i++) {
                        String value = valuesList.get(i);
                        String suffix = (i == valuesList.size() - 1) ? ";\n\n" : ",\n";
                        categoryWriter.write(value + suffix);
                        allWriter.write(value + suffix);
                    }
                }

                System.out.println("已生成SQL文件: " + sqlFileName + " (共" + valuesList.size() + "个问题)");
            }
        }
    }

    /**
     * 从问题列表字符串中提取各个问题
     */
    private static List<String> extractQuestions(String questionListStr) {
        List<String> questions = new ArrayList<>();

        // 匹配每个问题对象
        // 更精确的匹配模式，处理嵌套大括号的情况
        int bracketCount = 0;
        int start = -1;
        for (int i = 0; i < questionListStr.length(); i++) {
            char c = questionListStr.charAt(i);
            if (c == '{') {
                if (bracketCount == 0) {
                    start = i;
                }
                bracketCount++;
            } else if (c == '}') {
                bracketCount--;
                if (bracketCount == 0 && start != -1) {
                    String questionStr = questionListStr.substring(start, i + 1);
                    String sqlValue = createSqlValue(questionStr);
                    if (sqlValue != null) {
                        questions.add(sqlValue);
                    }
                    start = -1;
                }
            }
        }

        return questions;
    }

    /**
     * 从问题字符串创建SQL值
     */
    private static String createSqlValue(String questionStr) {
        // 提取问题内容
        String content = extractField(questionStr, "quContent");
        if (content == null) return null;

        // 提取分析内容
        String analysis = extractField(questionStr, "analysisMd");
        if (analysis == null) analysis = "";

        // 转义特殊字符
        content = escapeSql(content);
        analysis = escapeSql(analysis);

        // 默认值
        String tenantId = "000000";
        String type = "简答题";
        String difficulty = "中等";
        String answer = "";
        String status = "0";
        String delFlag = "0";
        String createDept = "103";
        String createBy = "1";
        String updateTime = "NOW()";
        String updateBy = "1";
        String remark = "NULL";

        // 构建SQL值字符串
        return String.format("('%s', '%s', '%s', '%s', @category_id, '%s', '%s', '%s', '%s', %s, %s, %s, %s, %s, %s)",
                tenantId, content, type, difficulty, answer, analysis, status, delFlag,
                createDept, createBy, updateTime, updateBy, updateTime, remark);
    }

    /**
     * 从JSON对象字符串中提取指定字段的值
     */
    private static String extractField(String jsonObject, String fieldName) {

        // 使用正则表达式匹配字段值，支持跨行和各种转义字符
        String patternStr = "\"" + fieldName + "\"\\s*:\\s*\"((?:[^\"\\\\]|\\\\.)*)\"";
        Pattern pattern = Pattern.compile(patternStr, Pattern.DOTALL);
        Matcher matcher = pattern.matcher(jsonObject);

        if (matcher.find()) {
            String value = matcher.group(1);
            // 处理转义字符
            value = value.replace("\\\"", "\"")
                         .replace("\\n", "\n")
                         .replace("\\r", "\r")
                         .replace("\\t", "\t")
                         .replace("\\\\", "\\")
                         .replace("\\/", "/");
            return value;
        }

        return null;
    }

    /**
     * 转义SQL字符串中的特殊字符
     */
    private static String escapeSql(String str) {
        if (str == null) {
            return "";
        }
        return str.replace("\\", "\\\\")     // 首先处理反斜杠
                 .replace("'", "''")        // 转义单引号
                 .replace("\n", "\\n")      // 转义换行符
                 .replace("\r", "\\r")      // 转义回车符
                 .replace("\t", "\\t")      // 转义制表符
                 .trim();
    }
}
